package com.statistics.cloudpower.dao;

import com.statistics.bean.Electricity;
import com.statistics.bean.MaxCurrentPower;
import com.statistics.cloudpower.entity.TblElectricityStatisticsDay;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

/**
 * Created by studio on 2017/4/8.
 */

@Repository
public interface TblElectricityStatisticsDayDAO extends JpaRepository<TblElectricityStatisticsDay,Integer> {

    @Query(value = "select max.room1_electricity-min.room1_electricity as room1_electricity," +
            "max.room2_electricity-min.room2_electricity as room2_electricity," +
            "max.room3_electricity-min.room3_electricity as room3_electricity," +
            "max.room4_electricity-min.room4_electricity as room4_electricity," +
            "max.room5_electricity-min.room5_electricity as room5_electricity," +
            "max.room7_electricity-min.room7_electricity as room7_electricity," +
            "max.room6_electricity-min.room6_electricity as room6_electricity," +
            "max.room8_electricity-min.room8_electricity as room8_electricity " +
            "from (select * from tbl_electricity_statistics_day  where mac = :mac and statistics_time > :start " +
            "and statistics_time < :end ORDER BY mac,offset DESC limit 1) as max," +
            "(select * from  tbl_electricity_statistics_day where mac = :mac and statistics_time > :start and " +
            "statistics_time < :end ORDER BY mac,offset limit 1) as min", nativeQuery = true)
    Electricity getSubtraction(@Param("mac")String mac,@Param("start") long start,@Param("end") long end);

    @Query(value = "SELECT max(room1_current) as room1_current, max(room2_current) as room2_current," +
            " max(room3_current) as room3_current, max(room4_current) as room4_current, max(room5_current) as room5_current," +
            " max(room6_current) as room6_current, max(room7_current) as room7_current," +
            " max(room8_current) as room8_current ,max(room1_power) as room1_power, max(room2_power) as room2_power," +
            " max(room3_power) as room3_power, max(room4_power) as room4_power," +
            " max(room5_power) as room5_power, max(room6_power) as room6_power, max(room7_power) as room7_power," +
            " max(room8_power) as room8_power FROM tbl_electricity_statistics_day WHERE " +
            "mac = :mac AND statistics_time > :start AND statistics_time < :end", nativeQuery = true)
    MaxCurrentPower getMaxCurrentPower(@Param("mac")String mac,@Param("start") long start,@Param("end") long end );
}
